﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{
    class PhieuNhapXuatCT:Database
    {
        #region ***** Fields & Properties *****
        private string _manhapxuat;
        public string manhapxuat
        {
            get
            {
                return _manhapxuat;
            }
            set
            {
                _manhapxuat = value;
            }
        }
        private string _mahang;
        public string mahang
        {
            get
            {
                return _mahang;
            }
            set
            {
                _mahang = value;
            }
        }
       
        private string _dvt;
        public string dvt
        {
            get
            {
                return _dvt;
            }
            set
            {
                _dvt = value;
            }
        }

        
        private double _dgnhapxuat;
        public double dgnhapxuat
        {
            get
            {
                return _dgnhapxuat;
            }
            set
            {
                _dgnhapxuat = value;
            }
        }

        private double _slchuaquydoi;
        public double slchuaquydoi
        {
            get
            {
                return _slchuaquydoi;
            }
            set
            {
                _slchuaquydoi = value;
            }
        }
        
        private string _chuthich;
        public string chuthich
        {
            get
            {
                return _chuthich;
            }
            set
            {
                _chuthich = value;
            }
        }
        private double _slquydoi;
        public double slquydoi
        {
            get
            {
                return _slquydoi;
            }
            set
            {
                _slquydoi = value;
            }
        }
        private double _slnhapxuat;
        public double slnhapxuat
        {
            get
            {
                return _slnhapxuat;
            }
            set
            {
                _slnhapxuat = value;
            }
        }
        private string _quycach;
        public string quycach
        {
            get
            {
                return _quycach;
            }
            set
            {
                _quycach = value;
            }
        }
        private double _phivanchuyen;
        public double phivanchuyen
        {
            get
            {
                return _phivanchuyen;
            }
            set
            {
                _phivanchuyen = value;
            }
        }
        private double _thanhtien;
        public double thanhtien
        {
            get
            {
                return _thanhtien;
            }
            set
            {
                _thanhtien = value;
            }
        }
        private double _chietkhau;
        public double chietkhau
        {
            get
            {
                return _chietkhau;
            }
            set
            {
                _chietkhau = value;
            }
        }
        private double _thuexuat;
        public double thuexuat
        {
            get
            {
                return _thuexuat;
            }
            set
            {
                _thuexuat = value;
            }
        }
        private double _slchuaxuat;
        public double slchuaxuat
        {
            get
            {
                return _slchuaxuat;
            }
            set
            {
                _slchuaxuat = value;
            }
        }
        private double _dgxuatvon;
        public double dgxuatvon
        {
            get
            {
                return _dgxuatvon;
            }
            set
            {
                _dgxuatvon = value;
            }
        }
        private int _stt;
        public int stt
        {
            get
            {
                return _stt;
            }
            set
            {
                _stt = value;
            }
        }
        
        #endregion

        #region ***** Init Methods *****
        public PhieuNhapXuatCT()
        {
        }
        public PhieuNhapXuatCT(string manhapxuat)
        {
            this.manhapxuat = manhapxuat;
        }
        //public PhieuNhapXuatCT(string manhapxuat, string mahang, string dvt, double dgnhapxuat, int slnhapxuat, double thanhtien, double chietkhau, double thanhtienchietkhau, double thue, double thanhtienthue)
        //{
        //    this.manhapxuat = manhapxuat;
        //    this.mahang = mahang;
        //    this.dvt = dvt;
        //    this.dgnhapxuat = dgnhapxuat;
        //    this.slnhapxuat = slnhapxuat;
        //    this.thanhtien = thanhtien;
        //    this.chietkhau = chietkhau;
        //    this.thanhtienchietkhau = thanhtienchietkhau;
        //    this.thue = thue;
        //    this.thanhtienthue = thanhtienthue;
           
        //}
        #endregion
        //public Boolean themPhieuNhapCT()
        //{
        //    Boolean ok = true;
        //    //them vao PhieuNhapXuatCT
        //    string sql = "insert into PhieuNhapXuatCT(manhapxuat,mahang,dvt,dgnhapxuat,slchuaquydoi,slquydoi,slnhapxuat,chietkhau,thanhtien,stt) ";

        //    sql += " values ('" + manhapxuat + "','" + mahang + "',N'" + dvt + "'," + dgnhapxuat + "," + slchuaquydoi + "," + slquydoi + "," + slnhapxuat + "," + chietkhau + "," + thanhtien + "," + stt + ")";
        //    if (executeQuery(sql) == false)
        //        ok = false;
        //    else
        //    {
        //        string sql1 = "insert into PhieuNhapCTTam(manhapxuat,mahang,dgnhap,slchuaxuat) ";
        //        sql1 += " values ('" + manhapxuat + "','" + mahang + "'," + (int)(dgnhapxuat / slquydoi) + "," + slnhapxuat + ")";
        //        if (executeQuery(sql1) == false)
        //            ok = false;
        //    }
        //    return ok;
        //}
        public Boolean themPhieuNhapXuatCT(string loainhapxuat)
        { 
            string sql = "";
            if (loainhapxuat == "PN")
            {
                sql = "insert into PhieuNhapXuatCT(manhapxuat,mahang,dvt,dgnhapxuat,slchuaquydoi,slquydoi,slnhapxuat,quycach,phivanchuyen,chietkhau,thuexuat,thanhtien,stt,slchuaxuat,dgxuatvon,chuthich) ";
                sql += " values ('" + manhapxuat + "','" + mahang + "',N'" + dvt + "'," + dgnhapxuat + "," + slchuaquydoi + "," + slquydoi + "," + slnhapxuat + ",N'" + quycach + "'," + phivanchuyen + "," + chietkhau + "," + thuexuat + "," + thanhtien + "," + stt + "," + slnhapxuat + "," + dgxuatvon + ",N'" + chuthich + "')";
             
            }
            else 
            {
                sql = "insert into PhieuNhapXuatCT(manhapxuat,mahang,dvt,dgnhapxuat,slchuaquydoi,slquydoi,slnhapxuat,quycach,phivanchuyen,chietkhau,thuexuat,thanhtien,stt,slchuaxuat,chuthich) ";
                sql += " values ('" + manhapxuat + "','" + mahang + "',N'" + dvt + "'," + dgnhapxuat + "," + slchuaquydoi + "," + slquydoi + "," + slnhapxuat + ",N'" + quycach + "'," + phivanchuyen + "," + chietkhau + "," + thuexuat + "," + thanhtien + "," + stt + "," + slnhapxuat + ",N'" + chuthich + "')";
            }
            return executeQueryTrans(sql);                      
        }
        public Boolean suaPhieuNhapXuatCT()
        {
            string sql = "update PhieuNhapXuatCT set dvt = N'" + dvt + "',dgnhapxuat = " + dgnhapxuat + ",slchuaquydoi = " + slchuaquydoi + ",slquydoi = " + slquydoi + ",slnhapxuat = " + slnhapxuat + ",chietkhau = " + chietkhau + ",thuexuat = " + thuexuat + ",thanhtien = " + thanhtien + ",slchuaxuat = " + slnhapxuat + ",dgxuatvon = " + dgxuatvon + " where mahang = '" + mahang + "' and manhapxuat = '" + manhapxuat + "' ";          
            return executeQuery(sql);
        }
        public Boolean suaSLPhieuNhapXuatCT()
        {
            string sql = "update PhieuNhapXuatCT set slchuaquydoi = slchuaquydoi + " + slchuaquydoi + ",slnhapxuat = slnhapxuat + " + slnhapxuat + " where mahang = '" + mahang + "' and manhapxuat = '" + manhapxuat + "' ";
            return executeQuery(sql);
        }
        public Boolean xoaPhieuNhapXuatCT(string manhapxuat, string mahang)
        {
            string sql = "delete PhieuNhapXuatCT where manhapxuat = '" + manhapxuat + "' and mahang = '" + mahang + "'";
            return executeQuery(sql);
        }
        
        public Boolean isExistPhieuNhapXuatCT(string manhapxuat, string mahang)
        {
            string sql = "select * from PhieuNhapXuatCT where manhapxuat = '" + manhapxuat + "' and mahang = '" + mahang + "'";
            return isExist(sql);
        }
        public DataTable theoDoiNhapXuatChiTietCuaHangHoa(string thongtindoanhnghiep,string tungay, string denngay, string mahang)
        {
            
            string sql = "select N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,nhom, manhapxuat,ngaynhapxuat,tenkhncc, mahang, tenhang, dvt, slchuaquydoi,dgnhapxuat, thanhtien,loai from ";
            sql += "( ";
            sql += "( ";
		    sql += "select N'Nhập hàng' as nhom, b.manhapxuat,ngaynhapxuat,tenkhncc, a.mahang, tenhang, b.dvt, slchuaquydoi,dgnhapxuat, thanhtien,loai ";
		    sql += "from HangHoa a left join PhieuNhapXuatCT b on a.mahang = b.mahang left join PhieuNhapXuat c on b.manhapxuat = c.manhapxuat  ";
            sql += "left join KhachHangNCC  d on c.makhncc = d.makhncc   where a.mahang = '" + mahang + "'  and loainhapxuat = 'PN' and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
	        sql += ") ";
	        sql += "UNION ALL ";

	        sql += "( ";
		    sql += "select N'Xuất hàng' as nhom,b.manhapxuat,ngaynhapxuat,tenkhncc, a.mahang, tenhang, b.dvt, slchuaquydoi,dgnhapxuat, thanhtien,loai  ";
		    sql += "from HangHoa a left join PhieuNhapXuatCT b on a.mahang = b.mahang left join PhieuNhapXuat c on b.manhapxuat = c.manhapxuat  ";
            sql += "left join KhachHangNCC  d on c.makhncc = d.makhncc where a.mahang = '" + mahang + "'  and loainhapxuat = 'PX' and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)  ";
	        sql += ") ";
	
            sql += ") a ";
            sql += "order by ngaynhapxuat";
            return getDataTable(sql);
        }
        public DataTable layDSPhieuNhapXuatCTTrongNgay(string loainhapxuat,string ngay,string mahang)
        {
            string sql = "select a.manhapxuat, ngaynhapxuat, mahang, slnhapxuat,CONVERT(INT,dgnhapxuat/slquydoi) as dgbanvon ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b ";
            sql += "where a.manhapxuat = b.manhapxuat and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'25/11/2011',103) and mahang = '" + mahang + "' ";
            sql += "and loainhapxuat = '" + loainhapxuat + "'";
            return getDataTable(sql);
        }
        


    }
}
